package org.compiere.pos;

import java.math.BigDecimal;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Date;
import java.util.List;

import org.compiere.db.CConnection;
import org.compiere.db.Database;
import org.compiere.model.MCash;
import org.compiere.model.MCashLine;
import org.compiere.model.MOrder;
import org.compiere.model.MPOS;
import org.compiere.model.MSysConfig;
import org.compiere.process.DocAction;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;

public class PosSaleOrderInterface extends SvrProcess {
	
	private Connection CONN_SOURCE;
	private Connection CONN_TARGET;
	private String ERROR_MSG = "Completed";
	private String RETURN_MSG = "";
	
	@Override
	protected String doIt() throws Exception {
		String res = "";
		/** 
		 * TODO: For loop before execute table : c_pos_db_server
		 */
		Statement stmt = CONN_TARGET.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM c_pos_db_server order by db_server_name");
		
		while(rs.next()){
			CConnection ccon = CConnection.get(Database.DB_POSTGRESQL, rs.getString("db_host"), rs.getInt("db_port"), rs.getString("db_name"));
			ccon.setDbUid(rs.getString("db_user_id"));
			ccon.setDbPwd(rs.getString("db_user_pwd"));
			CONN_SOURCE = ccon.getConnection(false, Connection.TRANSACTION_READ_COMMITTED);
			//System.out.println(rs.getString("db_server_name")+" : "+CONN_SOURCE);
			res = res + rs.getString("db_server_name")+" : "+this.runIt()+"\n\r";
		}
		return res;
	}
	
	protected String runIt() throws Exception {
		// TODO Auto-generated method stub
		this.prepareDataBeforInterface();
		
		// Statement For Query From Table c_pos_order
		PreparedStatement psmt_source = CONN_SOURCE.prepareStatement(" select * from c_pos_order where isinterfaced = 'P' ");
		PreparedStatement psmt_target = null;
		
		// Statement For Query From Table c_pos_orderline
		PreparedStatement psmt_source_orderline = CONN_SOURCE.prepareStatement(" select * from c_pos_orderline where c_order_id = ? ");
		PreparedStatement psmt_target_orderline = null;
		
		// Statement For Query From Table c_pos_ordertax
		PreparedStatement psmt_source_ordertax = CONN_SOURCE.prepareStatement(" select * from c_pos_ordertax where c_order_id = ? ");
		PreparedStatement psmt_target_ordertax = null;
		
		// Statement For Query From Table c_pos_payment
		PreparedStatement psmt_source_payment = CONN_SOURCE.prepareStatement(" select * from c_pos_payment where c_order_id = ? ");
		PreparedStatement psmt_target_payment = null;
		
		ResultSet rs = psmt_source.executeQuery();
		ResultSet rs_orderline = null;
		ResultSet rs_ordertax = null;
		ResultSet rs_payment = null;
		
		boolean isCreateSql = false;
		boolean isCreateSql_orderline = false;
		boolean isCreateSql_ordertax = false;
		boolean isCreateSql_payment = false;
		
		StringBuilder sql_ins = new StringBuilder();
		StringBuilder sql_column = new StringBuilder();
		StringBuilder sql_var = new StringBuilder();
		while (rs.next()) {
			if(!isCreateSql){
				sql_column = new StringBuilder();
				sql_var = new StringBuilder();
				for(int i=1;i<=rs.getMetaData().getColumnCount();i++){
					if(i > 1){
						sql_column.append(",");
						sql_var.append(",");
					}
					sql_column.append(rs.getMetaData().getColumnName(i));
					sql_var.append("?");
				}
				sql_ins.append("INSERT INTO c_pos_order (").append(sql_column).append(") VALUES (").append(sql_var).append(") ");
//				System.out.println("SQL c_order : "+sql_ins.toString());
				psmt_target = CONN_TARGET.prepareStatement(sql_ins.toString());
				isCreateSql = true;
			}
			
			for(int i=1;i<=rs.getMetaData().getColumnCount();i++){
				psmt_target.setObject(i, rs.getObject(rs.getMetaData().getColumnName(i)), rs.getMetaData().getColumnType(i));
			}
			
			// C_POS_ORDERLINE
			psmt_source_orderline.setObject(1, rs.getObject("c_order_id"));
			rs_orderline = psmt_source_orderline.executeQuery();
			
			while (rs_orderline.next()) {
				if(!isCreateSql_orderline){
					sql_ins = new StringBuilder();
					sql_column = new StringBuilder();
					sql_var = new StringBuilder();
					for(int i=1;i<=rs_orderline.getMetaData().getColumnCount();i++){
						if(i > 1){
							sql_column.append(",");
							sql_var.append(",");
						}
						sql_column.append(rs_orderline.getMetaData().getColumnName(i));
						sql_var.append("?");
					}
					sql_ins.append("INSERT INTO c_pos_orderline (").append(sql_column).append(") VALUES (").append(sql_var).append(") ");
//					System.out.println("SQL c_orderline : "+sql_ins.toString());
					psmt_target_orderline = CONN_TARGET.prepareStatement(sql_ins.toString());
					isCreateSql_orderline = true;
				}
				
				for(int i=1;i<=rs_orderline.getMetaData().getColumnCount();i++){
					psmt_target_orderline.setObject(i, rs_orderline.getObject(rs_orderline.getMetaData().getColumnName(i)), rs_orderline.getMetaData().getColumnType(i));
				}
				psmt_target_orderline.addBatch();
			}
			
			// C_POS_ORDERTAX
			psmt_source_ordertax.setObject(1, rs.getObject("c_order_id"));
			rs_ordertax = psmt_source_ordertax.executeQuery();
			
			while (rs_ordertax.next()) {
				if(!isCreateSql_ordertax){
					sql_ins = new StringBuilder();
					sql_column = new StringBuilder();
					sql_var = new StringBuilder();
					for(int i=1;i<=rs_ordertax.getMetaData().getColumnCount();i++){
						if(i > 1){
							sql_column.append(",");
							sql_var.append(",");
						}
						sql_column.append(rs_ordertax.getMetaData().getColumnName(i));
						sql_var.append("?");
					}
					sql_ins.append("INSERT INTO c_pos_ordertax (").append(sql_column).append(") VALUES (").append(sql_var).append(") ");
//					System.out.println("SQL c_ordertax : "+sql_ins.toString());
					psmt_target_ordertax = CONN_TARGET.prepareStatement(sql_ins.toString());
					isCreateSql_ordertax = true;
				}
				
				for(int i=1;i<=rs_ordertax.getMetaData().getColumnCount();i++){
					psmt_target_ordertax.setObject(i, rs_ordertax.getObject(rs_ordertax.getMetaData().getColumnName(i)), rs_ordertax.getMetaData().getColumnType(i));
				}
				psmt_target_ordertax.addBatch();
			}
			
			// C_POS_PAYMENT
			psmt_source_payment.setObject(1, rs.getObject("c_order_id"));
			rs_payment = psmt_source_payment.executeQuery();
			
			while (rs_payment.next()) {
				if(!isCreateSql_payment){
					sql_ins = new StringBuilder();
					sql_column = new StringBuilder();
					sql_var = new StringBuilder();
					for(int i=1;i<=rs_payment.getMetaData().getColumnCount();i++){
						if(i > 1){
							sql_column.append(",");
							sql_var.append(",");
						}
						sql_column.append(rs_payment.getMetaData().getColumnName(i));
						sql_var.append("?");
					}
					sql_ins.append("INSERT INTO c_pos_payment (").append(sql_column).append(") VALUES (").append(sql_var).append(") ");
//					System.out.println("SQL c_payment : "+sql_ins.toString());
					psmt_target_payment = CONN_TARGET.prepareStatement(sql_ins.toString());
					isCreateSql_payment = true;
				}
				
				for(int i=1;i<=rs_payment.getMetaData().getColumnCount();i++){
					psmt_target_payment.setObject(i, rs_payment.getObject(rs_payment.getMetaData().getColumnName(i)), rs_payment.getMetaData().getColumnType(i));
				}
				psmt_target_payment.addBatch();
			}
			
			psmt_target.addBatch();
		}
		try {
			if(isCreateSql){
				psmt_target.executeBatch();
				if(isCreateSql_orderline){
					psmt_target_orderline.executeBatch();
				}
				if(isCreateSql_ordertax){
					psmt_target_ordertax.executeBatch();
				}
				if(isCreateSql_payment){
					psmt_target_payment.executeBatch();
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		try {
			rs.close();
			rs_orderline.close();
			rs_ordertax.close();
			rs_payment.close();
			
			psmt_target.close();
			psmt_target_orderline.close();
			psmt_target_ordertax.close();
			psmt_target_payment.close();
			
			psmt_source.close();
			psmt_source_orderline.close();
			psmt_source_ordertax.close();
			psmt_source_payment.close();
		} catch (Exception e) {
			// TODO: handle exception
			//System.out.println("doIt Error : "+e.getMessage());
		}
		
		boolean isFinish = this.finishInterface();
		
//		return this.ERROR_MSG;
		return this.RETURN_MSG;
	}

	@Override
	protected void prepare() {
		// TODO Auto-generated method stub
		CONN_TARGET = DB.createConnection(false, Connection.TRANSACTION_READ_COMMITTED);
	}
	
	protected boolean prepareDataBeforInterface(){
		boolean isUpdate = false;
		StringBuilder sql_upd = new StringBuilder();
		sql_upd.append(" UPDATE c_pos_order ");
		sql_upd.append("    SET isinterfaced = 'P' ");
		sql_upd.append("	    ,taxinvoicefull = (SELECT inv.c_pos_tax_inv_id ");
		sql_upd.append("							 FROM c_pos_tax_inv inv ");
		sql_upd.append("							WHERE inv.documentno = c_pos_order.documentno) ");
		sql_upd.append("	    ,actualbpname = (SELECT inv.c_pos_tax_name ");
		sql_upd.append("							 FROM c_pos_tax_inv inv ");
		sql_upd.append("							WHERE inv.documentno = c_pos_order.documentno) ");
		sql_upd.append("		,taxinvoice_remark = (SELECT inv.c_pos_tax_remark ");
		sql_upd.append("								FROM c_pos_tax_inv inv ");
		sql_upd.append("							   WHERE inv.documentno = c_pos_order.documentno) ");
		sql_upd.append("  WHERE isinterfaced = 'N' ");
		sql_upd.append("    AND processed = 'Y' ");
		try {
			PreparedStatement psmt_upd = CONN_SOURCE.prepareStatement(sql_upd.toString());
			isUpdate = psmt_upd.execute();
			psmt_upd.close();
			CONN_SOURCE.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			System.out.println("PrepareDataBeforInterface Error : "+e.getMessage());
			isUpdate = false;
		}
		
		return isUpdate;
	}
	
	protected boolean finishInterface() throws SQLException{
		boolean isUpdate = false;
		
		StringBuilder sql_upd = new StringBuilder();
		try {
			sql_upd.append(" UPDATE c_pos_order ");
			sql_upd.append("    SET isinterfaced = 'Y', ");
			sql_upd.append("        interfaced_date = now() ");
			sql_upd.append("  WHERE isinterfaced = 'P' ");
			PreparedStatement psmt_upd = CONN_SOURCE.prepareStatement(sql_upd.toString());
			isUpdate = psmt_upd.execute();
			psmt_upd.close();
			CONN_TARGET.commit();
			CONN_SOURCE.commit();
			this.insertSaleOrder();
		} catch (Exception e) {
			// TODO: handle exception
//			this.ERROR_MSG = e.getMessage();
//			System.out.println("FinishInterface Error ---> "+e.getMessage());
		}
		
		return isUpdate;
	}
	
	protected String insertSaleOrder() {
		PreparedStatement psmt = null;
		try {
			psmt = CONN_TARGET.prepareCall(" SELECT interface_pos_order() ");
			StringBuilder sql = new StringBuilder();
			sql.append(" INSERT INTO c_pos_int_log ")
			   .append("            (c_pos_int_log_id, c_order_id, dateordered, docstatus, documentno, processmsg, created, updated, ad_client_id, ad_org_id, createdby, updatedby) ")
			   .append("     VALUES ((SELECT nextid((SELECT ad_sequence_id FROM ad_sequence WHERE name = 'c_pos_int_log')::Integer, 'N'::Varchar)),?, ?, ? , ?, ?, now(), now(),?,?,?,?) ");
			StringBuilder sqlPayment = new StringBuilder();
			sqlPayment.append(" SELECT met.c_cashbook_id, pay.payamt, ord.c_pos_id, pay.couponnumber ")
					  .append("   FROM c_pos_order ord, c_pos_payment pay, c_pos_payment_method met ")
					  .append(" WHERE ord.documentno = ? ")
					  .append("   AND pay.c_order_id = ord.c_order_id ")
					  .append("   AND pay.tendertype = met.paymentmethod ")
					  .append("   AND met.paymentmethod IN ('P', 'V') ");
			ResultSet rs = psmt.executeQuery();
//			System.out.println("Finish Execute on "+Calendar.getInstance().getTime());
			CONN_TARGET.commit();
			PreparedStatement psmtRs = CONN_TARGET.prepareStatement(sql.toString());
			PreparedStatement psmtPayment = CONN_TARGET.prepareStatement(sqlPayment.toString());
			boolean isProcessIntLog = false;
			while (rs.next()) {
				Array orderId = rs.getArray(1);
				String processMsg = "";
				BigDecimal[] big = (BigDecimal[])orderId.getArray();
				int infComplete = 0;
				int infError = 0;
				for(int i=0; i<big.length; i++){
					MOrder order = new MOrder(Env.getCtx(), big[i].intValue(), null);
					boolean process = true;
					try {
//						order.setDocAction(DocAction.ACTION_WaitComplete);
						process = order.processIt(MOrder.ACTION_Complete);
						processMsg = order.getProcessMsg();
					} catch (Exception e) {
						process = false;
						processMsg = e.getMessage();
						System.err.println("Error : "+e.getMessage());
					}
//					System.out.println("DocumentNo : "+order.getDocumentNo());
					if(!process){
//						this.ERROR_MSG = "Some Order has not completed.";
//						System.out.println("ProcessMsg : Error : "+processMsg);
						
						psmtRs.setObject(1, big[i].intValue(), Types.NUMERIC);
						psmtRs.setTimestamp(2, order.getDateOrdered());
						psmtRs.setString(3, (process) ? "Y" : "N");
						psmtRs.setString(4, order.getDocumentNo());
						psmtRs.setString(5, processMsg);
						psmtRs.setObject(6, order.getAD_Client_ID(), Types.NUMERIC);
						psmtRs.setObject(7, order.getAD_Org_ID(), Types.NUMERIC);
						psmtRs.setObject(8, order.getCreatedBy(), Types.NUMERIC);
						psmtRs.setObject(9, order.getUpdatedBy(), Types.NUMERIC);
						psmtRs.addBatch();
						isProcessIntLog = true;
						infError++;
					}else{
						order.setDocStatus(MOrder.ACTION_Complete);
						order.setDocAction(DocAction.ACTION_Close);
						order.save();
						infComplete++;
//						System.out.println("ProcessMsg : Finish : "+order.getProcessMsg());
						
						psmtPayment.setString(1, order.getDocumentNo());
						ResultSet rsPayment = psmtPayment.executeQuery();
						while (rsPayment.next()) {
							Integer c_cashbook_id = rsPayment.getInt(1);
							BigDecimal payamt = rsPayment.getBigDecimal(2);
							Integer c_pos_id = rsPayment.getInt(3);
							String couponnumber = rsPayment.getString(4);
							
							try {
								MPOS m_pos = MPOS.get(Env.getCtx(), c_pos_id);
								MCash cash = MCash.get(m_pos.getCtx(), c_cashbook_id, new Timestamp(new Date().getTime()), null);
								MCashLine cashLine = new MCashLine(cash);
								cashLine.setWHTaxAmt(new BigDecimal(0));
								cashLine.setCashType(MCashLine.CASHTYPE_Charge);
								cashLine.setVoucherNo(couponnumber);
								cashLine.setC_Charge_ID(MSysConfig.getIntValue("CHARGE_AS_MARKETING_EXPENSE", 0));
								cashLine.setAmount(payamt.multiply(new BigDecimal(-1)));
								cashLine.save();
							} catch (Exception e) {
								// TODO: handle exception
								System.err.println(e.getMessage());
//								e.printStackTrace();
							}
						}
						rsPayment.close();
					}
					
//					MOrderLine[] orderlineAr = order.getLines();
//					for(MOrderLine orderline: orderlineAr){
//						orderline.setProcessed(true);
//						orderline.save();
//					}
				}
				
				this.RETURN_MSG = "<br/> - Process : "+big.length+"<br/> - Completed : "+infComplete+"<br/> - Not Completed : "+infError;
			}
			
//			System.out.println("Finish Process on "+Calendar.getInstance().getTime());
			
			try {
				if(isProcessIntLog){
					int[] execB = psmtRs.executeBatch();
				}
				psmt.close();
				psmtRs.close();
				psmtPayment.close();
				CONN_TARGET.commit();
			} catch (Exception e) {
				// TODO: handle exception
//				System.err.println("XXX---------> "+e.getMessage());
				e.printStackTrace();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				CONN_TARGET.rollback();
				CONN_SOURCE.rollback();
				psmt.close();
				this.closeConection();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
//			this.ERROR_MSG = e.getMessage();
			this.RETURN_MSG = e.getMessage();
//			System.err.println("Error ---> "+e.getMessage());
		}
		return null;
	}
	
	protected void closeConection(){
		try {
			if(!CONN_TARGET.isClosed()){
				CONN_TARGET.close();
			}
			if(!CONN_SOURCE.isClosed()){
				CONN_SOURCE.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	@Override
	protected void finalize() throws Throwable {
		// TODO Auto-generated method stub
		super.finalize();
		try {
			CONN_TARGET.close();
			CONN_SOURCE.close();
		} catch (Exception e) {
			// TODO: handle exception
//			System.out.println("Error : "+e.getMessage());
		}
	}
	
	public String reprocessSaleOrder(List<Integer> p_orderIdLs){
		String retMsg = "";
		String processMsg = "";
		
		int procCompleted = 0;
		int procError = 0;
		
		StringBuilder sqlUpd = new StringBuilder();
		sqlUpd.append(" UPDATE c_pos_int_log ");
		sqlUpd.append("    SET docstatus = ? ");
		sqlUpd.append(" 	   ,processmsg = ? ");
		sqlUpd.append(" 	   ,updated = getdate() ");
		sqlUpd.append("  WHERE c_order_id = ? ");
		
		StringBuilder sqlPayment = new StringBuilder();
		sqlPayment.append(" SELECT met.c_cashbook_id, pay.payamt, ord.c_pos_id, pay.couponnumber ");
		sqlPayment.append("   FROM c_pos_order ord, c_pos_payment pay, c_pos_payment_method met ");
		sqlPayment.append("  WHERE ord.documentno = ? ");
		sqlPayment.append("    AND pay.c_order_id = ord.c_order_id ");
		sqlPayment.append("    AND pay.tendertype = met.paymentmethod ");
		sqlPayment.append("    AND met.paymentmethod IN ('P', 'V') ");
		
		//PreparedStatement pstm = null;
		PreparedStatement pstm = null;
		PreparedStatement psmtPayment = null;
		ResultSet rsPayment = null;
		try {
			pstm = DB.prepareStatement(sqlUpd.toString(), null);
			psmtPayment = DB.prepareStatement(sqlPayment.toString(), null);
			for(Integer orderId : p_orderIdLs){
				//System.out.println("order id : "+orderId);
				MOrder order = new MOrder(Env.getCtx(), orderId, null);
				boolean process = true;
				try {
//					order.setDocAction(DocAction.ACTION_WaitComplete);
					process = order.processIt(MOrder.ACTION_Complete);
					processMsg = order.getProcessMsg();
				} catch (Exception e) {
					process = false;
					processMsg = e.getMessage();
				}
				
				pstm.setString(1, (process) ? "Y" : "N");
				pstm.setString(2, processMsg);
				pstm.setInt(3, orderId);
				//pstm.executeUpdate();
				pstm.addBatch();
				
				if(process){
					order.setDocStatus(MOrder.ACTION_Complete);
					order.setDocAction(DocAction.ACTION_Close);
					order.save();
					procCompleted++;
					psmtPayment.setString(1, order.getDocumentNo());
					rsPayment = psmtPayment.executeQuery();
					while (rsPayment.next()) {
						Integer c_cashbook_id = rsPayment.getInt(1);
						BigDecimal payamt = rsPayment.getBigDecimal(2);
						Integer c_pos_id = rsPayment.getInt(3);
						String couponnumber = rsPayment.getString(4);
						
						try {
							MPOS m_pos = MPOS.get(Env.getCtx(), c_pos_id);
							MCash cash = MCash.get(m_pos.getCtx(), c_cashbook_id, new Timestamp(new Date().getTime()), null);
							MCashLine cashLine = new MCashLine(cash);
							cashLine.setWHTaxAmt(new BigDecimal(0));
							cashLine.setCashType(MCashLine.CASHTYPE_Charge);
							cashLine.setVoucherNo(couponnumber);
							cashLine.setC_Charge_ID(MSysConfig.getIntValue("CHARGE_AS_MARKETING_EXPENSE", 0));
							cashLine.setAmount(payamt.multiply(new BigDecimal(-1)));
							cashLine.save();
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
					rsPayment.close();
				}else{
					procError++;
				}
			}
			try {
				int[] batchUpd = pstm.executeBatch();
				retMsg = " Process : "+p_orderIdLs.size()+", Completed : "+procCompleted+", Not Completed : "+procError;
			} catch (Exception e) {
				e.printStackTrace();
			}
		} catch (Exception e) {
			e.printStackTrace();
			DB.close(pstm);
			DB.close(rsPayment, psmtPayment);
		}
		
		return retMsg;
	}
	
}
